﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace AscendEast_DataEntry
{
    class DataAccess
    {
        OleDbConnection dbconn;
        OleDbCommand dbcmd = new OleDbCommand();
        string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\db.mdb;Persist Security Info=False";
        //string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\Arindam\\Desktop\\ASCEND\\AscendEast_DataEntry\\AscendEast_DataEntry\\db.mdb;Persist Security Info=False";

        public bool CheckDuplicate(String table_name, String col_name, String primary_key)
        {
            bool duplicate_flag = false;
            String query = "select Count(*) from " + table_name + " where " + col_name + " = '" + primary_key + "'";
            //OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, constr);
            //OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            //DataTable dataTable = new DataTable();
            ////DataSet ds = new DataSet();
            //dAdapter.Fill(dataTable);
            //if(dataTable.Rows.Count > 0)
            //    duplicate_flag = true;
            dbconn = new OleDbConnection(constr);
            dbcmd.Connection = dbconn;
            dbcmd.CommandType = CommandType.Text;
            dbcmd.CommandText = query;
            dbcmd.Connection.Open();
            int count = int.Parse(dbcmd.ExecuteScalar().ToString());
            dbcmd.Connection.Close();
            if (count > 0)
                duplicate_flag = true;

            return duplicate_flag;
           
        }

        public bool Check_Duplicate(string query)
        {
            bool duplicate_flag = false;            
            //dbconn = new OleDbConnection(constr);
            //dbcmd.Connection = dbconn;
            //dbcmd.CommandType = CommandType.Text;
            //dbcmd.CommandText = query;
            //dbcmd.Connection.Open();
            //int count = int.Parse(dbcmd.ExecuteScalar().ToString());
            //dbcmd.Connection.Close();

            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, constr);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            DataTable dt = new DataTable();            
            dAdapter.Fill(dt);
            if (dt.Rows.Count > 0)
                duplicate_flag = true;
            
            return duplicate_flag;

        }

        public bool Insert(string query)
        {
            bool inserted_flag = false;
            dbconn = new OleDbConnection(constr);
            dbcmd.Connection = dbconn;
            dbcmd.CommandType = CommandType.Text;
            dbcmd.CommandText = query;
            dbcmd.Connection.Open();
            int temp = dbcmd.ExecuteNonQuery();
            dbcmd.Connection.Close();
            if (temp > 0)
                inserted_flag = true;

            return inserted_flag;
        }

        public DataTable Select(string query)
        {
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, constr);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            DataTable dt = new DataTable();            
            dAdapter.Fill(dt);

            return dt;
        }

        public bool Update(string query)
        {
            bool updated_flag = false;
            dbconn = new OleDbConnection(constr);
            dbcmd.Connection = dbconn;
            dbcmd.CommandType = CommandType.Text;
            dbcmd.CommandText = query;
            dbcmd.Connection.Open();
            int temp = dbcmd.ExecuteNonQuery();
            dbcmd.Connection.Close();
            if (temp > 0)
                updated_flag = true;

            return updated_flag;
        }

        public bool Delete(string query)
        {
            bool deleted_flag = false;
            dbconn = new OleDbConnection(constr);
            dbcmd.Connection = dbconn;
            dbcmd.CommandType = CommandType.Text;
            dbcmd.CommandText = query;
            dbcmd.Connection.Open();
            int temp = dbcmd.ExecuteNonQuery();
            dbcmd.Connection.Close();
            if (temp > 0)
                deleted_flag = true;

            return deleted_flag;
        }
    }
}
